package com.cinema.dao;

import com.cinema.entity.Seat;
import com.cinema.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 座位数据访问对象
 */
public class SeatDAO {
    
    /**
     * 根据影厅ID获取所有座位
     */
    public List<Seat> findByHallId(Integer hallId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Seat> seats = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT * FROM seats WHERE hall_id = ? ORDER BY row_num, seat_num";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, hallId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                seats.add(mapResultSetToSeat(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return seats;
    }
    
    /**
     * 获取场次的已预订座位
     */
    public List<Integer> getBookedSeatIds(Integer showtimeId) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Integer> bookedSeatIds = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT DISTINCT os.seat_id " +
                        "FROM order_seats os " +
                        "INNER JOIN orders o ON os.order_id = o.id " +
                        "WHERE o.showtime_id = ? AND o.status IN ('PENDING', 'PAID') " +
                        "AND os.status = 'BOOKED'";
            
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, showtimeId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                bookedSeatIds.add(rs.getInt("seat_id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return bookedSeatIds;
    }
    
    /**
     * 根据ID列表获取座位信息
     */
    public List<Seat> findByIds(List<Integer> seatIds) {
        if (seatIds == null || seatIds.isEmpty()) {
            return new ArrayList<>();
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Seat> seats = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            StringBuilder sql = new StringBuilder("SELECT * FROM seats WHERE id IN (");
            for (int i = 0; i < seatIds.size(); i++) {
                if (i > 0) sql.append(",");
                sql.append("?");
            }
            sql.append(") ORDER BY row_num, seat_num");
            
            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < seatIds.size(); i++) {
                pstmt.setInt(i + 1, seatIds.get(i));
            }
            
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                seats.add(mapResultSetToSeat(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return seats;
    }
    
    /**
     * 检查座位是否可用
     */
    public boolean areSeatsAvailable(List<Integer> seatIds, Integer showtimeId) {
        if (seatIds == null || seatIds.isEmpty()) {
            return false;
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        boolean available = false;
        
        try {
            conn = DBUtil.getConnection();
            
            // 检查座位是否存在且状态正常
            StringBuilder sql1 = new StringBuilder("SELECT COUNT(*) FROM seats WHERE id IN (");
            for (int i = 0; i < seatIds.size(); i++) {
                if (i > 0) sql1.append(",");
                sql1.append("?");
            }
            sql1.append(") AND status = 'AVAILABLE'");
            
            pstmt = conn.prepareStatement(sql1.toString());
            for (int i = 0; i < seatIds.size(); i++) {
                pstmt.setInt(i + 1, seatIds.get(i));
            }
            
            rs = pstmt.executeQuery();
            int availableCount = 0;
            if (rs.next()) {
                availableCount = rs.getInt(1);
            }
            
            if (availableCount != seatIds.size()) {
                return false;
            }
            
            // 检查座位是否已被预订
            rs.close();
            pstmt.close();
            
            StringBuilder sql2 = new StringBuilder("SELECT COUNT(*) FROM order_seats os " +
                    "INNER JOIN orders o ON os.order_id = o.id " +
                    "WHERE o.showtime_id = ? AND os.seat_id IN (");
            for (int i = 0; i < seatIds.size(); i++) {
                if (i > 0) sql2.append(",");
                sql2.append("?");
            }
            sql2.append(") AND o.status IN ('PENDING', 'PAID') AND os.status = 'BOOKED'");
            
            pstmt = conn.prepareStatement(sql2.toString());
            pstmt.setInt(1, showtimeId);
            for (int i = 0; i < seatIds.size(); i++) {
                pstmt.setInt(i + 2, seatIds.get(i));
            }
            
            rs = pstmt.executeQuery();
            int bookedCount = 0;
            if (rs.next()) {
                bookedCount = rs.getInt(1);
            }
            
            available = bookedCount == 0;
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return available;
    }
    
    /**
     * 自动分配座位
     */
    public List<Seat> autoAssignSeats(Integer hallId, Integer showtimeId, Integer count) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Seat> assignedSeats = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            
            // 获取未被预订的座位
            String sql = "SELECT s.* FROM seats s " +
                        "WHERE s.hall_id = ? AND s.status = 'AVAILABLE' " +
                        "AND s.id NOT IN (" +
                        "    SELECT DISTINCT os.seat_id FROM order_seats os " +
                        "    INNER JOIN orders o ON os.order_id = o.id " +
                        "    WHERE o.showtime_id = ? AND o.status IN ('PENDING', 'PAID') " +
                        "    AND os.status = 'BOOKED'" +
                        ") " +
                        "ORDER BY s.row_num, s.seat_num " +
                        "LIMIT ?";
            
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, hallId);
            pstmt.setInt(2, showtimeId);
            pstmt.setInt(3, count);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                assignedSeats.add(mapResultSetToSeat(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn, pstmt, rs);
        }
        
        return assignedSeats;
    }
    
    /**
     * 将ResultSet映射为Seat对象
     */
    private Seat mapResultSetToSeat(ResultSet rs) throws SQLException {
        Seat seat = new Seat();
        seat.setId(rs.getInt("id"));
        seat.setHallId(rs.getInt("hall_id"));
        seat.setRowNum(rs.getString("row_num"));
        seat.setSeatNum(rs.getInt("seat_num"));
        seat.setSeatType(rs.getString("seat_type"));
        seat.setStatus(rs.getString("status"));
        return seat;
    }
}
